Configuring User Fields and User Calculations

DEACOM allows the creation of user-defined fields that, once created, are available on various master data records such as items, Vendors, and Ship-to Companies. These fields allow companies to enter and store information outside of the fields provided in the system. For example, a user-defined field is a good option for bill of lading notes, since bills of lading can require additional notes that would not appear in the order or line note fields which are on other sales order documents. A user defined bill of lading field can be created and added to the bill of lading report.

User fields can be used with the DEACOM user calculation feature and are also available for printing on the majority of Part Forms and reports used throughout the system. The security setting "Tools -- edit user fields" controls the ability to add or modify user fields and the setting "Tools -- edit user calculations" controls the ability to add or modify user calculations. These security settings do not prevent users from entering information or values in these fields throughout the system.

Configuration

Prior to version 17.01.003 there is no prior configuration required to use User Fields or User Calculations. Beginning in version 17.01.003 two System Options were added that may be useful when defining user fields with a Field Type of "memo". These fields, "Memo Box Font Name", and "Memo Box Font Size", are used to define the font and size for all Memo boxes throughout the system, including user fields.

Process

Managing User Fields

Configuring User Fields

To add a new User Field, perform the following steps. Note that some fields are only available when selecting certain "FIle" options.

Navigate to Tools > Maintenance > User Fields.
Click "New" to open the Edit User Defined Fields form.
Make a selection from the "File" pick list.
Enter a Name for the new field and use that name to enter a Field Name. For example, if the field will be named "Future Risk", the Field Name should read "u_Future_Risk".
Select a "Field Type", which will dictate how the remaining fields are completed.
1. If creating a Character field, specify a "Picture" to indicate how many characters the User Field should accept.
2. If creating a Date, Logical, or Memo field, continue with step 6.
3. If creating a Numeric field, specify the Minimum and Maximum values allowed and a "Picture" to indicate how many digits the User Field should accept.
4. If creating a Pick List field, continue with step 6 and be sure to define the Pick List options after initially saving the record.
Check the flags as appropriate, referring to the User Fields Encyclopedia page for information on how they function.
Click "Save" to create the new field.
1. If creating a Pick List field, click the "Edit Pick List" button to open the Edit Pick List form.
2. Click "New" to add a new pick list option, provide a "Name", and ensure the "Active" flag is checked.
3. Click "Next" to continue adding pick list options.
4. When finished, save and exit the Edit Pick List Item and Edit Pick List forms.
Verify the User Field displays on records as desired by navigating to the User Fields tab of the appropriate area of DEACOM, for example Sales > Order Entry > User Fields tab.

Notes:

Calculations in User Fields work differently than User Calculations. If calculations are built based on other User Fields, they will not calculate until the prior User Field(s) have values in them, which will happen after the first time the order is saved.
The "Fire Sales Calculations Upon Change" flag may be selected when using a "File" of Sales Order Header as well as for Sales Order Line user fields. If this flag is checked, the system will re-fire User Calculations on the Sales Order applicable to this order when the value in the User Field is modified. For example, a customer adds charges to Sales Orders based on the values input in certain Sales Order header User Fields. In this example, the values in the User Fields are normally entered or updated when printing out the sales pick list when drivers pull up to the shipping window. In this case, the calculations need to re-fire upon User Field change so that the appropriate values are printed.
When deactivating a pick list value users will be prompted if they wish to clear the pick list option for any records in which it is already set.
The Default Value, Default Memo, and Default Search Value fields can be used to set the appropriate defaults on User Fields, thus allowing users to define the exceptions rather than having them set all values for all fields. This saves times in setup and can help prevent errors.

Sorting User Field display order

If many user-defined fields have been created over time and they need to be sorted to create a cleaner, more organized look, or so they appear in a particular order (i.e. alphabetical), follow these steps:

Navigate to Tools > Maintenance > User Fields.
Select a record assigned to the File type that needs sorting and click "Modify". For example, if you wish to sort the Sales Order Header user-defined fields, select a record with a File of "Sales Order Header".
On the Edit User Defined Fields form, click the "Sort" button, which will open a Sort form listing all User Fields for the same File type.
Use the Sort form to put the fields in the desired order then save and exit the Sort form and the Edit User Defined Fields form.
Verify the order is as desired by navigating to the User Fields tab of the appropriate area of DEACOM, in this case, Sales > Order Entry > User Fields tab.

Completing User Fields on records

Once one or more User Fields have been added, they should be completed on the related records. To do this:

Open the desired record that the User Field relates to and navigate to the User Fields tab. Any fields set for the type of record being modified will be displayed in this tab.
Fill in the data as necessary (make pick list selections, enter dates, values, or memos) then save and exit the form.

Notes:

If it is desired to have Item Master User Fields printed on the Bill of Materials (Formula) report, these fields must be prefixed with "cUSERPROD".
in_Engineering fields are available for use in calculated Sales Order line user fields.
All user-defined fields on Formulas (BOM Revisions) are available to print on batch tickets.
Item Master User Fields are available to the grid on the Issue/Reserve Inventory form throughout the system.
Beginning in version 15, all Purchase Order user-defined fields are available to the Purchase Order reports, such as Purchase Order confirmation, Receiver, and Purchase Order.

Managing User Calculations

Creating a non-stock item

A non-stock part must be created before a User Calculation can work. This part defines the name of the fee or charge as well as the appropriate revenue account to be charged. This part is required for the creation of a new line item on the desired record. To create the non-stock item, refer to the process detailed in the Designing a Part Numbering System and Creating Parts page and adhere to the following:

Be sure to use a "Part Number" that clearly defines the name of the fee or charge that will be added to the order.
Ensure the "Active" and "Saleable", "Purchasable", "Manufactured", or "Allow On BOMs" flags are checked, depending on the type of User Calculation being created, on the Item Master Properties tab.
Select the appropriate "Revenue", "Purchase To", or "Material" accounts on the Item Master Accounts tab so that the line's amount is mapped in the GL correctly.

Configuring User Calculations

To add a new User Calculation, perform the following steps. Note that some fields are only available when selecting certain "Table" options. Refer to the link in Step 1. below for additional information regarding the options available when creating User Calculations.

Navigate to Tools > Maintenance > User Calculations.
Click "New" to open the Edit Calculations form.
Make a selection from the "Table" pick list.
Enter a Description for the new calculation and use that name to enter a Field Name. For example, if the field will be named "Transportation Charge", the Field Name should read "u_Transportation_Charge". Important: For the User Calculation to select the correct item when adding a line to the BOM, Job, or Sales/Purchase Order, the "Description" of User Calculation must match the "Part Number" of the item that will be added to the BOM/Job/Sales Order/Purchase Order.
Select a "Field Type", which will dictate how the remaining fields are completed. For example, Character or Text calculations can be used on SDS forms that require large amounts of text descriptions and regulatory information
Select a "Type" and enter a "Picture" to indicate how many characters or digits the Calculation should display.
Enter the Expression to be used for the Calculation. Information on the fields available can be found on the User Calculations Encyclopedia page.
Complete other fields depending on the "Table" selected.
1. If creating a BOM calculation, make a selection for the "Level" which determines at what BOM level the calculation will take effect. Note: BOM Calcs will not fire if the Part is not flagged as "Stocked" on its Item Master Properties tab.
2. If creating a Job calculation, enter a date in the "Retro Since" field to determine the starting date when using the "Retroactive" button.
3. If creating a Purchasing calculation, make a selection for (1) "Calculated Field" and enter a date in the (2) "Retro Since" field, which (1) determines whether the User Calc will calculate the price or quantity on new lines added to orders and (2) determines the starting date when using the "Retroactive" button.
4. If creating a Sales calculation, make a selection for (1) "Calculated Field" and (2) "Recalc Until", and enter a date in the (3) "Retro Since" field, which determine (1) whether the User Calc will calculate the price or quantity on new lines added to orders, (2) at which point in the sales order to cash process the calculation will cease to recalculate, and (3) determines the starting date when using the "Retroactive" button.
Check the flags as appropriate, referring to the User Calculations Encyclopedia page for information on how they function.
Click "Save" to create the new calculation.
Verify the User Calculation applies to records as desired by completing the appropriate processes. Details can be found on the Adding and Modifying BOM Revisions, Entering Production Jobs Using the Job Entry Function, Entering Purchase Orders Using the Order Entry Function, and Entering Sales Orders Using the Order Entry Function pages.

Note on the "Retro Since" field and the "Retroactive" button: Once a User Calculation has been added, it can be retroactively applied to previously created BOMs, Jobs, Purchase Orders, and Sales Orders based on the "Retro Since" date specified. This action occurs when a user clicks the "Retroactive" button on the desired calculation. The "Retroactive" button changes all BOMs, Job Types, Purchase Order Types, and Sales Order Types (depending on the "Table" selection) and, for orders, also modifies orders in all statuses. This includes Not Received, Received, Invoiced, and Paid (for Purchasing) and Not Shipped, Shipped, Invoiced, and Paid (for Sales). Historical copies of these orders are updated as well. Use this button with caution as the action cannot be undone. Additionally, if adding BOM calcs after BOMs/Formulas have been created in the system, users will need to obtain and run the a "RecalcFormulas" External from Deacom Support.

Sorting User Calculation display order

If many user-defined calculations have been created over time and they need to be sorted to create a cleaner, more organized look, or so they appear in a particular order (i.e. alphabetical), follow these steps:

Navigate to Tools > Maintenance > User Calculations.
Select a record assigned to the Table type that needs sorting and click "Modify". For example, if you wish to sort the Sales Order user-defined calculations, select a record with a Table of "Sales Order".
On the Edit Calculations form, click the "Sort" button, which will open a Sort form listing all User Calculations for the same Table type.
Use the Sort form to put the calculations in the desired order then save and exit the Sort form and the Edit Calculations form.
Verify the order is as desired by completing the appropriate processes. Details can be found on the Adding and Modifying BOM Revisions, Entering Production Jobs Using the Job Entry Function, Entering Purchase Orders Using the Order Entry Function, and Entering Sales Orders Using the Order Entry Function pages.

Applying User Calculations to records

Once one or more User Calculations have been added, they will automatically be added on the related records that conform to the data set on the calculation record.

FAQ & Diagnostic Tips

What do I do if I have duplicate user fields?

User fields can be duplicated because of data importing. Some of these values can be fixed without an external. For the duplicate values, clear the value so that the user field is blank and then click save. The numeric fields will require two saves: once to change the value to 0, another to actually clear the value. Logical fields cannot be fixed this way since they cannot be cleared and will require an external to be fixed.

The following query can be used to identify duplicate records in the item master:

SELECT d1_title, pr_codenum, d2_value FROM dmd1, dtd2, dmprod WHERE d1_id = d2_d1id AND d2_recid = pr_id AND d1_table = 'dmprod' AND CAST(d2_d1id AS CHAR) + CAST(d2_recid AS CHAR) IN (SELECT CAST(d2_d1id AS CHAR) + CAST(d2_recid AS CHAR) FROM dtd2 GROUP BY CAST(d2_d1id AS CHAR) + CAST(d2_recid AS CHAR) HAVING COUNT(d2_id) > 1)

The query can be tweaked for each table you want to see duplicates on by changing the text below in italics appropriately.

SELECT d1_title, bi_name, d2_value FROM dmd1, dtd2, dmbill WHERE d1_id = d2_d1id AND d2_recid = bi_id AND d1_table = 'dmbill' AND CAST(d2_d1id AS CHAR) + CAST(d2_recid AS CHAR) IN (SELECT CAST(d2_d1id AS CHAR) + CAST(d2_recid AS CHAR) FROM dtd2 GROUP BY CAST(d2_d1id AS CHAR) + CAST(d2_recid AS CHAR) HAVING COUNT(d2_id) > 1)

Some transactions display asterisks in user-defined fields instead of the correct values. What causes this and how do I fix it?

Asterisks are displayed instead of values in user-defined fields when the user calculation fails to execute for any reason. To fix this, navigate to Tools > Maintenance > User Calculations. Click on the user-defined calculation in question and click "Modify". Verify that the "Expression" is entered correctly. Then, enter a "Retro since" date and click "Retroactive". The "Retro since" date is the date encompassing the transactions you wish to recalculate.

I am getting this message "Column names in each table must be unique. Column name in table is specified more than once. The current transaction has been canceled." What is causing this?

Confirm the field in the message is not named the same as one of your user calculations or user fields.

TIP: Sales order calculations are enabled in DSD beginning in version 17.02.010. The calcs will fire, and store against the orders the same as in main application. Calculations will also add parts to orders when saved the same as in the main application. This occurs when the device is online. If the device is not online, the system will track what calculations need to be fired, and fire/sync them when the device is back online.